﻿<#**********************************************************************************************************************************
 *    Name: CheckSQLCertificate.ps1
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Check that the default instance of SQL Server is configured to use a valid server certificate.
#>
$Result= 0;
$Status= @{
    0 = "OK"                       ;
    1 = "Certificate Not Found"    ;
    2 = "Certificate Expired"      ;
    4 = "Subject/Hostname Mismatch"};

# build the default instance registry path and fetch the currently configured certificate's thumbprint
$MSSQLServer  = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" -Name MSSQLServer).MSSQLServer;
$SQLThumbprint= (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$MSSQLServer\MSSQLServer\SuperSocketNetLib" -Name Certificate).Certificate;

# is SQL Server configured to use a certificate?
if (-not [string]::IsNullOrEmpty($SQLThumbprint)) {

    # get the localhost's FQDN
    $Computer= Get-WmiObject Win32_ComputerSystem;
    $ComputerFQDN= "{0}.{1}" -f $Computer.DNSHostName,$Computer.Domain;

    # search the certificate store for $SQLThumbprint
    $Certificate= (Get-ChildItem -Path "Cert:\LocalMachine\My") | Where-Object {$_.Thumbprint -eq $SQLThumbprint};
    if (-not $Certificate)                                                                          {$Result= ($Result -bor 1)}  # "Certificate Not Found"
    else {
        if (-not ($Certificate.NotBefore -le (Get-Date) -and (Get-Date) -le $Certificate.NotAfter)) {$Result= ($Result -bor 2)}; # "Certificate Expired"
        if (-not ($Certificate.Subject -eq "CN=$ComputerFQDN"))                                     {$Result= ($Result -bor 4)}; # "Subject/Hostname Mismatch"
    };
};

Write-Output $Result;
if ($Result -ne 0) {
    $MessageBody= $Status.Keys | Sort-Object | where {$_ -band $Result} | foreach {"`r$($Status.Get_Item($_))"};
    Write-Output $MessageBody;
 
    # send notification
    $Message= New-Object System.Net.Mail.MailMessage("$Env:ComputerName@$env:UserDNSDomain", "DBANotifications@US.BBAAviation.com", "Watchdog+ Alert: Certificate", $MessageBody);
    $Message.ReplyTo= "DBANotifications@US.BBAAviation.com";
    $Message.Priority= "High";
    $smtp= New-Object System.Net.Mail.SMTPClient("smtp.bbaaviation.net");
    $smtp.Send($Message);
};